/**
 * Program   :   CodeAssistTools.java
 * Author    :   shadow guo
 * Create    :   2013-6-5 上午7:08:43
 *
 * Copyright 2008 by Tongfu Information Technology Co. Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information
 * of Tongfu Information Technology Co. Ltd. ("Confidential Information").  You
 * shall not disclose such Confidential Information and shall use
 * it only in accordance with the terms of the license agreement
 * you entered into with Tongfu Information Technology Co. Ltd.
 * 
 */
package com.tongfusoft.web.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.yaml.snakeyaml.Yaml;

import com.tongfusoft.da.help.DBUtils;
import com.tongfusoft.utils.io.WriteFileUtils;
import com.tongfusoft.web.core.data.BaseData;

public class CodeAssistTools {
	private List<Field> columns;
	private String tableName;
	private String pkName;

	public static String lastUpdDate = "lastUpdDate";
	public static String createdDate = "createdDate";
	public static String createdBy = "createdBy";
	public static String status = "status";

	public String getTableName() {
		return tableName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public String getPkName() {
		return pkName;
	}

	public void setPkName(String pkName) {
		this.pkName = pkName;
	}

	private Connection getConn() throws FileNotFoundException, SQLException,
			ClassNotFoundException {
		Yaml yaml = new Yaml();
		ClassLoader loader = Thread.currentThread().getContextClassLoader();
		URL url = loader.getResource("da.yaml");
		File file = new File(url.getFile());
		InputStream input = new FileInputStream(file);
		Map<String, Object> map = (Map<String, Object>) yaml.load(input);

		String dfDb = (String) map.get("default");
		if (map.containsKey(dfDb)) {
			Map param = (Map) ((Map) map.get(dfDb)).get("datasource");
			Properties prop = new Properties();
			prop.put("user", param.get("username"));
			prop.put("password", param.get("password"));
			prop.put("url", param.get("url"));

			Class.forName((String) param.get("driver"));
			Connection conn = DriverManager.getConnection(
					(String) param.get("url"), prop);

			return conn;
		}

		return null;
	}

	private List<Field> getAllField() throws FileNotFoundException,
			SQLException, ClassNotFoundException {
		String sql = "select * from " + tableName;
		Statement stmt = getConn().createStatement();
		ResultSet result = stmt.executeQuery(sql);

		// 获取源数据中所有字段名称
		ResultSetMetaData rsmd = result.getMetaData();
		List<Field> fields = new ArrayList<Field>();

		for (int i = 1; i <= rsmd.getColumnCount(); i++) {
			Field field = new Field();
			field.setFieldName(rsmd.getColumnName(i));
			field.setFieldType(rsmd.getColumnTypeName(i));
			field.setScale(rsmd.getScale(i));

			fields.add(field);
		}

		result.close();
		stmt.close();

		return fields;
	}
	
	private void processSb(StringBuffer sb){
		int count = sb.length();
		for(int i=count-1;i>=0 ;i--){
			if(sb.charAt(i) != ' ' || sb.charAt(i) != '\r' || sb.charAt(i) != '\n'){
				if(sb.charAt(i) == ','){
					sb.deleteCharAt(i);
					return;
				}
			}
		}
	}

	private String getInsertSQL() {
		int index = 0;
		StringBuffer sb = new StringBuffer();
		sb.append("---").append("\r\n");
		sb.append("insert#").append(tableName).append(" : |").append("\r\n");
		sb.append("    ");
		sb.append("insert into ").append(tableName).append(" (");
		for (int i = 0; i < columns.size(); i++) {
			Field column = columns.get(i);

			if (!StringUtils.equalsIgnoreCase(column.getWarpName(), status)) { 
				sb.append(column.getFieldName()).append(",");

				if (++index % 5 == 0) {
					sb.append("\r\n").append("      ");
				}
			}

		}

		processSb(sb);

		sb.append(" ) ").append("\r\n").append("       values ( ");

		index = 0;
		for (int i = 0; i < columns.size(); i++) {
			Field column = columns.get(i);
			if (!StringUtils.equalsIgnoreCase(column.getWarpName(), status)) { // status不insert
				sb.append("#{").append(column.getWarpName()).append(",")
						.append(column.getJavaType()).append(",")
						.append(column.getJdbcType()).append("}").append(",");
				
				if (++index % 5 == 0 ) {
					sb.append("\r\n").append("      ");
				}
			}
		}

		processSb(sb);

		sb.append(" ) ").append("\r\n");

		return sb.toString();
	}

	private String getUpdateSQL() {
		int index = 0;
		StringBuffer sb = new StringBuffer();
		sb.append("---").append("\r\n");

		sb.append("update#").append(tableName).append(" : |").append("\r\n");
		sb.append("    ");
		sb.append("update ").append(tableName).append(" set ");

		String where = "";

		for (int i = 0; i < columns.size(); i++) {
			Field column = columns.get(i);

			if (!StringUtils.equalsIgnoreCase(pkName, column.getFieldName()) ) {
				if(!StringUtils.equalsIgnoreCase(column.getWarpName(),
							status)
					&& !StringUtils.equalsIgnoreCase(column.getWarpName(),
							createdDate)
					&& !StringUtils.equalsIgnoreCase(column.getWarpName(),
							createdBy)){
						sb.append(column.getFieldName()).append("=").append("#{")
							.append(column.getWarpName()).append(",")
							.append(column.getJavaType()).append(",")
							.append(column.getJdbcType()).append("}")
							.append(",");

					if (++index % 5 == 0) {
						sb.append("\r\n").append("      ");
					}
				}
			} else {
				where = column.getFieldName() + "= #{" + column.getWarpName()
						+ "} ";
			}

		}

		processSb(sb);
		
		sb.append("\r\n");
		sb.append("      ").append("where ").append(where).append("\r\n");

		return sb.toString();
	}

	private String getDeleteSQL() {
		StringBuffer sb = new StringBuffer();
		sb.append("---").append("\r\n");

		sb.append("delete#").append(tableName).append(" : |").append("\r\n");
		sb.append("    ");
		sb.append("delete from ").append(tableName).append(" where ");

		for (int i = 0; i < columns.size(); i++) {
			Field column = columns.get(i);

			if (StringUtils.equalsIgnoreCase(pkName, column.getFieldName())) {
				sb.append(column.getFieldName()).append(" = ").append("#{")
						.append(column.getWarpName()).append("}");
				break;
			}
		}

		sb.append("\r\n");

		return sb.toString();
	}
	
	private String getDeleteSqlByUpdate(){
		StringBuffer sb = new StringBuffer();
		sb.append("---").append("\r\n");

		sb.append("delete#").append(tableName).append(" : |").append("\r\n");
		sb.append("    ");
		//sb.append("delete from ").append(tableName).append(" where ");
		sb.append("update ").append(tableName).append(" set status = '0' where  ");
		
		for (int i = 0; i < columns.size(); i++) {
			Field column = columns.get(i);

			if (StringUtils.equalsIgnoreCase(pkName, column.getFieldName())) {
				sb.append(column.getFieldName()).append(" = ").append("#{")
						.append(column.getWarpName()).append("}");
				break;
			}
		}

		sb.append("\r\n");

		return sb.toString();
	}

	public void printSQL() {
		StringBuffer sb = new StringBuffer();
		sb.append(this.getInsertSQL());
		sb.append(this.getUpdateSQL());
		sb.append(this.getDeleteSqlByUpdate());
		System.out.println(sb.toString());
	}

	public void init(String tableName, String pkName)
			throws FileNotFoundException, SQLException, ClassNotFoundException {
		this.tableName = tableName;
		this.pkName = pkName;
		columns = getAllField();
	}

	public String getBean() throws FileNotFoundException {
		StringBuffer sb = new StringBuffer();
		String warpPkName = "";

		for (int i = 0; i < columns.size(); i++) {
			Field column = columns.get(i);

			if (!StringUtils.equals(column.getWarpName(),
					BaseData.FIELD_CREATEDBY)
					&& !StringUtils.equals(column.getWarpName(),
							BaseData.FIELD_CREATEDDATE)
					&& !StringUtils.equals(column.getWarpName(),
							BaseData.FIELD_LASTUPDBY)
					&& !StringUtils.equals(column.getWarpName(),
							BaseData.FIELD_LASTUPDDATE)) {
				// NUMBER,DATE,VARCHAR2
				if (StringUtils.equals(column.getFieldType(), "DATE")) {
					sb.append("private Date ").append(column.getWarpName())
							.append(";").append("\r\n");
				} else if (StringUtils.equals(column.getFieldType(), "NUMBER")) {
					if (column.getScale() == 0) {
						sb.append("private int ").append(column.getWarpName())
								.append(";").append("\r\n");
					} else {
						sb.append("private double ")
								.append(column.getWarpName()).append(";")
								.append("\r\n");
					}
				} else {
					sb.append("private String ").append(column.getWarpName())
							.append(";").append("\r\n");
				}

				if (StringUtils.equalsIgnoreCase(pkName, column.getFieldName())) {
					warpPkName = "\"" + column.getWarpName() + "\"";
				}
			}
		}

		Yaml yaml = new Yaml();

		String content = "";
		for (Object config : yaml.loadAll(this.getClass().getResourceAsStream(
				"/bean.yaml"))) {
			Map<String, Object> properties = (Map<String, Object>) config;
			if (properties.containsKey("bean")) {
				content = (String) properties.get("bean");
				break;
			}
		}

		content = String.format(content, warpTableName(), sb.toString(),
				warpPkName, "\"" + tableName + "\"") + "\r\n";

		return content;
	}

	private String warpTableName() {
		String[] warpNames = StringUtils.split(tableName.toLowerCase(), "_");
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < warpNames.length; i++) {
			sb.append(warpNames[i].substring(0, 1).toUpperCase());
			if (warpNames[i].length() > 1) {
				sb.append(warpNames[i].substring(1));
			}
		}
		return sb.toString();
	}

	private List<Map<String, String>> getAllTableNames() {
		return DBUtils
				.executeSelectDynamic("select cu.table_name,cu.COLUMN_NAME from user_cons_columns cu,"
						+ " user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P'");
	}

	private String getAllSQLs(List<Map<String, String>> tables)
			throws FileNotFoundException, SQLException, ClassNotFoundException {
		StringBuffer sb = new StringBuffer();
		for (Map<String, String> table : tables) {
			init(table.get("TABLE_NAME"), table.get("COLUMN_NAME"));
			sb.append(this.getInsertSQL());
			sb.append(this.getUpdateSQL());
			sb.append(this.getDeleteSqlByUpdate());
		}

		return sb.toString();
	}

	private String getAllBeans(List<Map<String, String>> tables)
			throws FileNotFoundException, SQLException, ClassNotFoundException {
		StringBuffer sb = new StringBuffer();
		for (Map<String, String> table : tables) {
			init(table.get("TABLE_NAME"), table.get("COLUMN_NAME"));
			sb.append(this.getBean());
		}

		return sb.toString();
	}

	private void getAllData() throws UnsupportedEncodingException,
			FileNotFoundException, IOException, SQLException,
			ClassNotFoundException {
		List<Map<String, String>> tables = this.getAllTableNames();
		WriteFileUtils.writeFile("sql.yaml", this.getAllSQLs(tables));
		WriteFileUtils.writeFile("bean.txt", this.getAllBeans(tables));
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		CodeAssistTools tools = new CodeAssistTools();

		if (args.length == 1 && args[0].equals("ALL")) {
			try {
				tools.getAllData();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else if (args.length == 2) {
			try {
				tools.init(args[0], args[1]);
				tools.printSQL();
				System.out.println(tools.getBean());
			} catch (Exception e) {
				e.printStackTrace();
			}
		} else {
			System.out
					.println("方法1：输入参数ALL，表示打印所有的表（仅对oracle数据库），生成所有的bean。例子：CodeAssistTools ALL");
			System.out
					.println("方法2：输入两个参数，表名和主键字段名,表名和字段名都需要是大写。例子：CodeAssistTools TSM_USERS USER_ID");
		}
	}

	public class Field {
		String fieldName;
		String fieldType;
		int scale;

		public int getScale() {
			return scale;
		}

		public void setScale(int scale) {
			this.scale = scale;
		}

		public String getFieldName() {
			return fieldName;
		}

		public void setFieldName(String fieldName) {
			this.fieldName = fieldName;
		}

		public String getFieldType() {
			return fieldType;
		}

		public void setFieldType(String fieldType) {
			this.fieldType = fieldType;
		}

		public String getWarpName() {
			if (StringUtils.equals("LAST_UPD_DATE", fieldName.toUpperCase())) {
				return CodeAssistTools.lastUpdDate;
			} else if (StringUtils.equals("CREATED_DATE",
					fieldName.toUpperCase())) {
				return CodeAssistTools.createdDate;
			} else {
				String[] warpNames = StringUtils.split(fieldName.toLowerCase(),
						"_");
				StringBuffer sb = new StringBuffer();
				for (int i = 0; i < warpNames.length; i++) {
					if (i == 0) {
						sb.append(warpNames[i]);
					} else {
						sb.append(warpNames[i].substring(0, 1).toUpperCase()
								+ warpNames[i].substring(1));
					}

				}
				return sb.toString();
			}
		}

		public String getJavaType() {
			if (this.getFieldType().equals("VARCHAR")
					|| this.getFieldType().equals("VARCHAR2")
					|| this.getFieldType().equals("CHAR")) {
				return "javaType=java.lang.String";
			} else if (this.getFieldType().equals("DATE")) {
				return "javaType=java.util.Date";
			} else {
				if (this.getScale() == 0) {
					return "javaType=java.lang.Integer";
				} else {
					return "javaType=java.lang.Double";
				}
			}
		}

		public String getJdbcType() {
			if (this.getFieldType().equals("VARCHAR")
					|| this.getFieldType().equals("VARCHAR2")
					|| this.getFieldType().equals("CHAR")) {
				return "jdbcType=VARCHAR";
			} else if (this.getFieldType().equals("NUMBER")) {
				return "jdbcType=NUMERIC";
			} else {
				return "jdbcType=TIME";
			}
		}
	}
}
